/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.elcom.emocbc.dbmapping.persistence;

import com.elcom.emocbc.dbmapping.tables.pojos.Bsc;
import com.elcom.emocbc.dbmapping.tables.Zone;
import com.elcom.emocbc.dbmapping.tables.ZoneBsc;
import com.elcom.emocbc.dbmapping.tables.daos.ZoneBscDao;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;
import org.jooq.Configuration;
import org.jooq.ConnectionProvider;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.conf.StatementType;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;

/**
 *
 * @author GiangLT
 */
public class ZoneBscPersistence extends ZoneBscDao {

    public ZoneBscPersistence() throws SQLException {
        ConnectionProvider connectionProvider = new DbConnectionProvider();
        Settings settings = new Settings();
        settings.setStatementType(StatementType.STATIC_STATEMENT);
        Configuration configuration = new DefaultConfiguration()
                .set(connectionProvider)
                .set(SQLDialect.ORACLE)
                .set(settings);
        this.setConfiguration(configuration);
    }
    private final ZoneBsc ZONE_BSC = ZoneBsc.ZONE_BSC;
    private final com.elcom.emocbc.dbmapping.tables.Bsc BSC = com.elcom.emocbc.dbmapping.tables.Bsc.BSC;

    public List<Bsc> getBscsFromZoneName(String zoneName) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        final Zone ZONE = Zone.ZONE;
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context
                    .select()
                    .from(BSC)
                    .join(ZONE_BSC).on(ZONE_BSC.BSC_ID.equal(BSC.BSC_ID))
                    .join(ZONE).on(ZONE_BSC.ZONE_ID.equal(ZONE.ZONE_ID))
                    .where(ZONE.ZONE_NAME.equal(zoneName))
                    .fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        } finally {
            DbAccess.closeConnection(con);
        }
    }
    
    public List<Bsc> getBscsFromZoneId(Long zoneId) throws SQLException {
        Connection con = DbAccess.getInstance().getConnection();
        try {
            DSLContext context = DSL.using(con, SQLDialect.ORACLE);
            return context
                    .select()
                    .from(BSC)
                    .join(ZONE_BSC).on(ZONE_BSC.BSC_ID.equal(BSC.BSC_ID))
                    .where(ZONE_BSC.ZONE_ID.equal(zoneId))
                    .fetchInto(com.elcom.emocbc.dbmapping.tables.pojos.Bsc.class);
        } finally {
            DbAccess.closeConnection(con);
        }
    }
//
//    @Override
//    protected String getInsertSql(ZoneBscRecord record, DSLContext context) throws SQLException {
//        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
//    }
}
